#include "database.h"

#include <QRegularExpression>

database::database()
{

    if(!init())
        qDebug()<<"fail to init the database";

        /***************(1)的解决方式***************/
        //添加数据库驱动
        if(QSqlDatabase::contains("connection1"))
            db = QSqlDatabase::database("connection1");
        else
            db = QSqlDatabase::addDatabase("QSQLITE", "connection1");
        //设置数据库名称（数据库存放的目录以及要创建的数据库的名称）
        //可以指定位置，我把原来位置指定 代码删了
        //db.setDatabaseName("C:/Users/18137/Desktop/HTTP3/HTTP/sqlq_name.db");
        db.setDatabaseName("Report_data.db");
        /***************(1)的解决方式***************/

        //打开数据库
        if(!db.open())
        {
            QMessageBox::critical(0,"cannot open data","Unable to establish a database connection.",QMessageBox::Cancel);
            //return false;
        }
        /***************(2)的解决方式***************/
        /***指定db****/
        //以下执行相关sql语句
        QSqlQuery query(db);

        /***************(2)的解决方式***************/



        //新建讲座和通知表格
    //    query.exec("create table notice(notice_time varchar,link varchar primary key)");
     //   query.exec("create table report(title varchar,name varchar,time varchar,place varchar,college varchar,link varchar,primary key(title,name,time),FOREIGN KEY(link) REFERENCES report(link))");
query.exec("create table notice(notice_time varchar(200)not null,link varchar(100)not null primary key)");
query.exec("create table report(title varchar(50)not null,name varchar(50)not null,time varchar(40)not null,place varchar(80)not null,college varchar(20)not null,link varchar(100)not null,primary key(title,name,time),FOREIGN KEY(link) REFERENCES report(link))");

query.exec("create view information_list as SELECT title,name,time,place,college,report.link,notice_time FROM report join notice ON report.link=notice.link order by notice_time desc");

query.exec("create table system (update_time varchar primary key) ");




        //return true;
        /*
    //向表中插入3条记录
    query.exec("insert into student values(1,'xiaogang')");
    query.exec("insert into student values(2,'xiaoming')");
    query.exec("insert into student values(3,'xiaohong')");
    query.exec("insert into student values(4,'xiaohao')");
*/

        /*
    //添加数据库驱动
    QSqlDatabase db1;
    if(QSqlDatabase::contains("connection2"))
        db1 = QSqlDatabase::database("connection2");
    else
        db1 = QSqlDatabase::addDatabase("QSQLITE", "connection2");
    //设置数据库名称
    db1.setDatabaseName("sqlq_name2.db");

        //打开数据库
        if(!db1.open())
        {
            QMessageBox::critical(0,"cannot open data","Unable to establish a database connection.",QMessageBox::Cancel);
            return false;
        }

        //以下执行相关sql语句
        QSqlQuery query1(db1);

        //新建student表，id设置为主键，还有一个name项
        query1.exec("create table student(id int primary key,name varchar)");

        //向表中插入3条记录
        query1.exec("insert into student values(1,'xiaogang1')");
        query1.exec("insert into student values(2,'xiaoming1')");
        query1.exec("insert into student values(3,'xiaohong1')");
        query1.exec("insert into student values(4,'xiaohao1')");
        return true;
        */

}


bool database::init()
{
    rowNum=0;
    //建立数据库变量
    return true;

}

bool database::insert_notice(QString t1,QString t2)
{
    QSqlQuery query(db);
    QVariantList notice_time1,link1;
    query.prepare("INSERT INTO notice (notice_time,link) "
                  "VALUES (?,?)");
    notice_time1<<t1;
    link1<<t2;



    query.addBindValue(notice_time1);
    query.addBindValue(link1);


    if (!query.execBatch())
        qDebug() << query.lastError()<<"database table notice insert error";   //这里后面可以加一个返回false值

    return  true;
}

bool database::insert_report(QString t1,QString t2,QString t3,QString t4,QString t5,QString t6)
{
    QSqlQuery query(db);
    QVariantList title1,lecturer1,time1,place1,colleage1,link1;
    query.prepare("INSERT INTO report (title,name,time,place,college,link) "
                  "VALUES (?,?,?,?,?,?)");


    //dealing_report_time(t3,t5);
    //t3=dealing_report_time(t3,t5);

    title1<<t1;
    lecturer1<<t2;
    time1<<t3;
    place1<<t4;
    colleage1<<t5;
    link1<<t6;



    query.addBindValue(title1);
    query.addBindValue(lecturer1);
    query.addBindValue(time1);
    query.addBindValue(place1);
    query.addBindValue(colleage1);
    query.addBindValue(link1);

    if (!query.execBatch())
        qDebug() << query.lastError()<<"database report insert error";


}

bool database::insert_system(QString t1)
{
    QSqlQuery query(db);
    QVariantList update_time1;
    query.prepare("INSERT INTO system (update_time) "
                  "VALUES (?)");
    update_time1<<t1;




    query.addBindValue(update_time1);


    if (!query.execBatch())
        qDebug() << query.lastError()<<"database table system insert error";   //这里后面可以加一个返回false值

    return  true;
}

QString database::dealing_report_time(QString time, QString college)
{
    /*
    QRegularExpression regularExpression("年月日*");

    int index = 0;
      \
    QString temp=regularExpression

    match
*/
    // 采用QRegExp正则bai表达式duzhi
    // 使用空格替dao换"/"
    QString temp1 = time.replace(QRegExp("年"), "-");
    QString temp2 = time.replace(QRegExp("月"), "-");
    QString temp3 = time.replace(QRegExp("日"), "");

    return temp3;
}

bool database::show()
{
    QSqlQuery query(db);
    query.exec("select * from report");
    while(query.next())
    {
        //query.value(0)是id的值，将其转换为int型
        for(int i=0;i<=5;i++)
        {
            QString value = query.value(i).toString();
            //输出两个值
            qDebug() << value ;
        }

    }

}

bool database::show_notice()
{
    QSqlQuery query(db);
    query.exec("select * from notice");
    while(query.next())
    {
        //query.value(0)是id的值，将其转换为int型
        for(int i=0;i<=1;i++)
        {
            QString value = query.value(i).toString();
            //输出两个值
            qDebug() << value ;
        }

    }

}

int database::getRowNum()
{
    QSqlQuery query(db);

    query.exec("select * from report");
    int numRows;
    //判断数据库驱动是否支持QuerySize特性，若支持，使用size()函数获取总行数
    if(db.driver()->hasFeature(QSqlDriver::QuerySize))
    {
        qDebug()<<"has feature : query size";
        numRows = query.size();

    }
    else
    {
        qDebug()<<"no feature : query size";
        query.last();
        numRows = query.at() + 1;
        qDebug()<<query.at();
    }
    rowNum=numRows;

    //输出行数
    qDebug()<<"row number :"<< rowNum;


    return numRows;
}

int database::getRowNum_notice()
{
    QSqlQuery query(db);

    query.exec("select * from notice");
    int numRows;
    //判断数据库驱动是否支持QuerySize特性，若支持，使用size()函数获取总行数
    if(db.driver()->hasFeature(QSqlDriver::QuerySize))
    {
        qDebug()<<"has feature : query size";
        numRows = query.size();

    }
    else
    {
        qDebug()<<"no feature : query size";
        query.last();
        numRows = query.at() + 1;
        qDebug()<<query.at();
    }
    rowNum=numRows;

    //输出行数
    qDebug()<<"row number :"<< rowNum;


    return numRows;
}

int database::getRowNum_system()
{
    QSqlQuery query(db);

    query.exec("select * from system");
    int numRows;
    //判断数据库驱动是否支持QuerySize特性，若支持，使用size()函数获取总行数
    if(db.driver()->hasFeature(QSqlDriver::QuerySize))
    {
        qDebug()<<"has feature : query size";
        numRows = query.size();

    }
    else
    {
        qDebug()<<"no feature : query size";
        query.last();
        numRows = query.at() + 1;
        qDebug()<<query.at();
    }
    rowNum=numRows;

    //输出行数
    qDebug()<<"table system row number :"<< rowNum;


    return numRows;
}



QSqlDatabase database::getDataBase()
{

    return db;
}
